package com.qfedu.mtl.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.qfedu.mtl.util.handler.ResultSetHandler;

import javax.sql.DataSource;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;

/**
 * 数据操作工具类
 * 1. 快速获得连接。
 * 2. 释放资源。
 * 3. 执行增删改。
 * 4. 执行查询。
 * 5. 提供事务管理能力
 * a. 开启事务
 * b. 提交事务
 * c. 回滚
 * 6.整合连接池( 获得连接对象，直接从连接池获得 )
 * DataSource
 */
public class DBUtil {

    //声明一个连接池
    private static DataSource dataSource;

    //声明一个ThreadLocal变量，实现对每个调用线程建立 连接对象的副本
    private static ThreadLocal<Connection> local = new ThreadLocal<>();

    //声明一个集合保存配置文件信息
    private static Properties configData = new Properties();

    /**
     * 加载配置文件
     */
    static {
        try {
            configData.load(DBUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
        } catch (IOException e) {
            throw new RuntimeException("配置加载失败");
        }
        //初始化durid的连接池
        dataSource = new DruidDataSource();
        //配置
        ((DruidDataSource) dataSource).configFromPropety(configData);
    }

    /**
     * 获得一个数据库连接对象
     *
     * @return connection
     */
    public static Connection getConn() {
        //当前线程中没有连接对象，则创建。
        if (local.get() == null) {
            /*String username = configData.getProperty("jdbc.username");
            String password = configData.getProperty("jdbc.password");
            String url = configData.getProperty("jdbc.url");
            //配置文件
            Connection connection  = null;
            try {
                connection = DriverManager.getConnection(url,username,password);
            } catch (SQLException e) {
                throw new RuntimeException("创建连接失败");
            }*/
            Connection connection = null;
            try {
                connection = dataSource.getConnection();//直接从连接池中获取一个可用的
            } catch (SQLException e) {
                throw new RuntimeException("获取连接失败:" + e.getMessage());
            }
            //添加到线程
            local.set(connection);
        }
        //返回当前线程中的连接
        return local.get();
    }

    /**
     * 释放资源
     *
     * @param rs
     * @param st
     * @param conn
     */
    public static void release(ResultSet rs, Statement st, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
                local.remove();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 执行DML语句的通用方法(insert update delete)
     *
     * @param sql    待执行的sql
     * @param conn   连接数据库对象
     * @param params sql参数
     * @return 执行影响行数
     */
    public static int executeDML(String sql, Connection conn, Object... params) {
        //声明语句对象
        PreparedStatement psmt = null;
        try {
            //创建预编译sql语句对象
            psmt = conn.prepareStatement(sql);
            //检验占位符 和 实际参数 个数匹配问题。
            if (psmt.getParameterMetaData().getParameterCount() != params.length) {
                throw new RuntimeException("参数个数不匹配");
            }
            //替换占位符
            for (int i = 0; i < params.length; i++) {
                psmt.setObject(i + 1, params[i]);
            }
            //执行sql语句
            int row = psmt.executeUpdate();

            return row;
        } catch (SQLException e) {
            throw new RuntimeException("SQL执行失败" + e.getMessage());
        } finally {
            DBUtil.release(null, psmt, conn);
        }
    }


    /**
     * 执行insert 返回自增主键值
     * @param sql
     * @param conn
     * @param params
     * @return
     */
    public static int executeInsert(String sql, Connection conn, Object... params) {
        int autoId = -1 ;
        //声明语句对象
        PreparedStatement psmt = null;
        try {
            //创建预编译sql语句对象
            psmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            //检验占位符 和 实际参数 个数匹配问题。
            if (psmt.getParameterMetaData().getParameterCount() != params.length) {
                throw new RuntimeException("参数个数不匹配");
            }
            //替换占位符
            for (int i = 0; i < params.length; i++) {
                psmt.setObject(i + 1, params[i]);
            }
            //执行sql语句
            psmt.executeUpdate();
            ResultSet generatedKeys = psmt.getGeneratedKeys();
            if( generatedKeys.next() ){
                  autoId = generatedKeys.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException("SQL执行失败" + e.getMessage());
        } finally {
            DBUtil.release(null, psmt, conn);
        }
        return autoId;
    }



    public static int executeDML(String sql, Object... params) {
        //声明语句对象
        PreparedStatement psmt = null;
        try {
            //创建预编译sql语句对象
            psmt = getConn().prepareStatement(sql);
            //检验占位符 和 实际参数 个数匹配问题。
            if (psmt.getParameterMetaData().getParameterCount() != params.length) {
                throw new RuntimeException("参数个数不匹配");
            }
            //替换占位符
            for (int i = 0; i < params.length; i++) {
                psmt.setObject(i + 1, params[i]);
            }
            //执行sql语句
            int row = psmt.executeUpdate();
            return row;
        } catch (SQLException e) {
            throw new RuntimeException("SQL执行失败" + e.getMessage());
        } finally {
            DBUtil.release(null, psmt, null);
        }
    }

    //封装一个通用查询方法
    //a. 这个方法返回值类型是什么?  ResultSet ..... Object ...       T   List<T>
    //b. sql conn params  executeDQL("select...", conn, DBUtil.class   )
    public static <T> T executeDQL(String sql, Connection conn, ResultSetHandler<T> handler, Object... params) {
        PreparedStatement psmt = null;
        ResultSet res = null;
        try {
            psmt = conn.prepareStatement(sql);
            //赋值
            for (int i = 0; i < params.length; i++) {
                psmt.setObject(i + 1, params[i]);
            }
            //执行sql
            res = psmt.executeQuery();
            //处理res结果集合,因为我不知道这个结果集合的情况，不能获取数据
            //我们的需求是明确的，我给你一个  ResultSet 你给我一个对象 或者对象集合。
            T result = handler.mapperTo(res);
            return result;
        } catch (SQLException e) {
            throw new RuntimeException("查询执行失败:" + e.getMessage());
        } finally {
            DBUtil.release(res, psmt, conn);
        }
    }

    public static <T> T executeDQL(String sql, ResultSetHandler<T> handler, Object... params) {
        PreparedStatement psmt = null;
        ResultSet res = null;
        try {
            psmt = getConn().prepareStatement(sql);
            //赋值
            for (int i = 0; i < params.length; i++) {
                psmt.setObject(i + 1, params[i]);
            }
            //执行sql
            res = psmt.executeQuery();
            //处理res结果集合,因为我不知道这个结果集合的情况，不能获取数据
            //我们的需求是明确的，我给你一个  ResultSet 你给我一个对象 或者对象集合。
            T result = handler.mapperTo(res);
            return result;
        } catch (SQLException e) {
            throw new RuntimeException("查询执行失败:" + e.getMessage());
        } finally {
            DBUtil.release(res, psmt, null);
        }
    }

    /**
     * 开启事务
     */
    public static void beginTransaction() {
        try {
            getConn().setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 提交事务
     */
    public static void commit() {
        try {
            getConn().commit();
            //关闭
            DBUtil.release(null, null, getConn());
            local.remove();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 回滚事务
     */
    public static void rollback() {
        try {
            getConn().rollback();
            //关闭
            DBUtil.release(null, null, getConn());
            local.remove();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 生成实体类文件
     */
    public static void generate(
            String path,
            String pkg,
            String tablePrefix,
            boolean enableUnderLine2Camel,
            String... tables) throws SQLException {
        Connection conn = getConn();
        String sql = "show tables";
        PreparedStatement statement = conn.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        List<String> allTable = new ArrayList<>();
        while (resultSet.next()) {
            allTable.add(resultSet.getString(1));
        }
        for (String table : allTable) {
            writeJavaSourceFile(table, tablePrefix, enableUnderLine2Camel, path, pkg);
        }
    }

    /**
     * 生成Java源文件
     * @param tableName
     * @param prefix
     * @param useCamel
     * @param path
     * @param packageName
     * @throws SQLException
     */
    private static void writeJavaSourceFile(String tableName, String prefix, boolean useCamel, String path, String packageName) throws SQLException {
        Connection conn = getConn();
        String sql = "desc ".concat(tableName);
        PreparedStatement statement = conn.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        Map<String, String> tableInfo = new HashMap<>();
        while (resultSet.next()) {
            String field = resultSet.getString("Field");
            String type = resultSet.getString("Type");
            tableInfo.put(useCamel ? underLine2Camel(field) : field, sqlType2Java(type));
        }
        File dir = new File(path, packageName.replace(".", "\\"));
        if (!dir.exists()){
            dir.mkdirs();
        }

        String javaClassName = firstChar2Upper(underLine2Camel(tableName.replaceFirst(prefix, "")));
        System.out.println(javaClassName);
        try( BufferedWriter bufferedWriter =
                     new BufferedWriter(new FileWriter( new File(dir, javaClassName.concat(".java")  ) ))) {
            StringBuilder sb = new StringBuilder();
            sb.append("package ".concat(packageName).concat(";\n"));
            sb.append("import java.util.Date;\n");
            sb.append("import java.math.BigDecimal;\n");
            sb.append("public class ".concat(javaClassName).concat("{\n"));
            for (String filed : tableInfo.keySet()) {
                sb.append("private ".concat( tableInfo.get(filed)).concat(" ").concat( filed ).concat(";\n") );
            }
            for (String filed : tableInfo.keySet()) {
                sb.append("public ".concat(tableInfo.get(filed)).concat(" get").concat(firstChar2Upper(filed)).concat("(){ \n"));
                sb.append("     return this.".concat(filed).concat(";\n"));
                sb.append("}\n");


                sb.append("public void".concat(" set").concat(firstChar2Upper(filed)).concat("(").concat(tableInfo.get(filed)).concat("  ").concat(filed).concat("){\n"));
                sb.append("      this.".concat(filed).concat(" = ").concat(filed).concat(";\n"));
                sb.append("}\n");
            }
            //toSring()方法
           // sb.append("\t@Override\n\tpublic  String  toString() {\n"+ "\t\t\treturn \""+ tableName +"{\"") ;

            sb.append("}");



            bufferedWriter.write(sb.toString());
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 数据库类型转Java类型
     * @param sqlType
     * @return
     */
    private static String sqlType2Java(String sqlType) {
        if (sqlType.contains("varchar")) {
            return "String";
        }
        if (sqlType.contains("char")) {
            return "String";
        }
        if (sqlType.contains("int")) {
            return "Integer";
        }
        if (sqlType.contains("bigint")) {
            return "Long";
        }
        if (sqlType.contains("float")) {
            return "Float";
        }
        if (sqlType.contains("double")) {
            return "Double";
        }
        if (sqlType.contains("decimal")) {
            return "BigDecimal";
        }
        if (sqlType.contains("date")) {
            return "Date";
        }
        if (sqlType.contains("datetime")) {
            return "Date";
        }
        if (sqlType.contains("timestamp")) {
            return "Date";
        }
        return "Object";
    }

    /**
     * 下划线转驼峰
     *
     * @param str
     * @return
     */
    public static String underLine2Camel(String str) {
        StringBuilder sb = new StringBuilder(str);
        int index = 0;
        while ((index = sb.indexOf("_", index)) != -1) {
            sb.delete(index, index + 1);
            sb.setCharAt(index, Character.toUpperCase(sb.charAt(index)));
        }
        return sb.toString();
    }

    /**
     * 首字母转大写
     *
     * @param str
     * @return
     */
    private static String firstChar2Upper(String str) {
        StringBuilder sb = new StringBuilder(str);
        sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
        return sb.toString();
    }

    public static void main(String[] args) throws SQLException {
        String path = "E:\\CQ-Java-2303\\Code_workplace\\mtl\\src";
        String pkg = "com.qfedu.mtl.entity";
        String tablePrefix = "tb_";
        boolean enableUnderLine2Camel = true;
        DBUtil.generate(path, pkg, tablePrefix, enableUnderLine2Camel);
    }


}
